import 'dart:async';

import 'package:flutterdemo/library/sqflite/article_provider.dart';
import 'package:flutterdemo/library/sqflite/compare_provider.dart';
import 'package:flutterdemo/library/sqflite/face_provider.dart';
import 'package:flutterdemo/library/sqflite/face_quality_provider.dart';
import 'package:flutterdemo/library/sqflite/person_provider.dart';
import 'package:sqflite/sqflite.dart';

class SqfliteHelper {
  Database _database;

  //饿汉式单例
  static SqfliteHelper _instance = SqfliteHelper._();

  //工厂模式，单例公开访问点
  factory SqfliteHelper() => _getInstance();

  //私有构造
  SqfliteHelper._();

  static SqfliteHelper _getInstance() {
    return _instance;
  }

  Future<Database> get db async {
    if (_database == null) {
      _database = await init();
    }
    return _database;
  }

  Future<Database> init() async {
    String path = await getDatabasesPath();
    path = '$path/flutter_demo.db';
    _database = await openDatabase(
      path,
      version: 2,
      singleInstance: false,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
      onDowngrade: _onDowngrade,
    );
    return _database;
  }

  Future<Database> initDatabase(String databaseName) async {
    String path = await getDatabasesPath();
    path = '$path/$databaseName';
    Database database = await openDatabase(
      path,
      version: 2,
      singleInstance: false,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
      onDowngrade: _onDowngrade,
    );
    return database;
  }

  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    Batch batch = db.batch();
    if (oldVersion == 1) {
      batch.execute('alter table ta_person add fire text');
    } else if (oldVersion == 2) {
      batch.execute('alter table ta_person add water text');
    } else if (oldVersion == 3) {}
    oldVersion++;
    //升级后版本还低于当前版本，继续递归升级
    if (oldVersion < newVersion) {
      _onUpgrade(db, oldVersion, newVersion);
    }
    await batch.commit();
  }

  Future<void> _onCreate(Database db, int version) async {
    Batch batch = db.batch();
    batch.execute(ArticleProvider().createSql);
    batch.execute(PersonProvider().createSql);
    batch.execute(CompareProvider().createSql);
    batch.execute(FaceQualityProvider().createSql);
    batch.execute(FaceQualityProvider().createIndexSql);
    batch.execute(FaceModelProvider().createSql);
    await batch.commit();
  }

  Future<void> _onDowngrade(Database db, int oldVersion, int newVersion) async {
    Batch batch = db.batch();

    await batch.commit();
  }

  Future<bool> isTableExists(String table) async {
    String sql =
        "select * from Sqlite_master where type='table' and name= '$table'";
    var result = await _database.rawQuery(sql);
    return result != null && result.length > 0;
  }

  tableName() async {
    String sql = "select * from Sqlite_master where type='table' ";
    var result = await _database.rawQuery(sql);
    result.forEach((element) {
      print(element.toString());
    });
  }
}
